package com.springBoot.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import static org.apache.poi.ss.usermodel.FillPatternType.SOLID_FOREGROUND;

/**
 * @author chenkuan
 * @version v1.0
 * @desc Excel导出工具
 * @date 2019/11/11 011 15:10
 */
@Component
public class ExportExcelUtil<T> {
	// 2003 版本 最大支持65536 行
	public static final String EXCEL_FILE_2003 = "2003";
	// 2007 版本以上 最大支持1048576行
	public static final String EXCEL_FILE_2007 = "2007";

	private static final String DATE_TIME_PATTERN = "dd/MM/yyyy HH:mm:ss";

	private Logger logger = LoggerFactory.getLogger(getClass());

	/**
	 * 导出无头部标题行Excel
	 * 时间格式默认：dd/MM/yyyy HH:mm:ss
	 *
	 * @param title    表格标题
	 * @param renders  表格标题对应的字段集合
	 * @param dataList 数据集合
	 * @param out      输出流
	 * @param version  2003 或者 2007，默认生成2003版本
	 */
	public void exportExcel(String title, List<String> renders, Collection<T> dataList, OutputStream out, String version) {
		if (EXCEL_FILE_2007.equals(version)) {
			exportExcel2007(title, null, renders, dataList, out, DATE_TIME_PATTERN);
		} else {
			exportExcel2003(title, null, renders, dataList, out, DATE_TIME_PATTERN);
		}
	}

	/**
	 * 导出带有头部标题行的Excel
	 * 时间格式默认：dd/MM/yyyy HH:mm:ss
	 *
	 * @param title    表格标题
	 * @param headers  头部标题集合
	 * @param renders  表格标题对应的字段集合
	 * @param dataList 数据集合
	 * @param out      输出流
	 * @param version  2003 或者 2007，默认生成2003版本
	 */
	public void exportExcel(String title, List<String> headers, List<String> renders, Collection<T> dataList, OutputStream out, String version) {
		if (EXCEL_FILE_2007.equals(version)) {
			exportExcel2007(title, headers, renders, dataList, out, DATE_TIME_PATTERN);
		} else {
			exportExcel2003(title, headers, renders, dataList, out, DATE_TIME_PATTERN);
		}
	}

	/**
	 * 导出带有头部标题行的Excel 下载文件流
	 * 时间格式默认：dd/MM/yyyy HH:mm:ss
	 *
	 * @param title    表格标题
	 * @param headers  头部标题集合
	 * @param renders  表格标题对应的字段集合
	 * @param dataList 数据集合
	 * @param response 响应
	 * @param version  2003 或者 2007，默认生成2003版本
	 */
	public void exportExcel(String title, List<String> headers, List<String> renders, Collection<T> dataList, HttpServletResponse response, String version) {
		try {
			response.setContentType("application/vnd.ms-excel");
			if (EXCEL_FILE_2007.equals(version)) {
				response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(title, "UTF-8") + ".xlsx");
				exportExcel2007(title, headers, renders, dataList, response.getOutputStream(), DATE_TIME_PATTERN);
			} else {
				response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(title, "UTF-8") + ".xls");
				exportExcel2003(title, headers, renders, dataList, response.getOutputStream(), DATE_TIME_PATTERN);
			}
		} catch (Exception e) {
			logger.error("Export Excel {} Exception", title);
		}
	}

	/**
	 * 通用Excel导出方法,利用反射机制遍历对象的所有字段，将数据写入Excel文件中
	 * 此版本生成2007以上版本的文件 (文件后缀：xlsx)
	 *
	 * @param title    表格标题名
	 * @param headers  表格头部标题集合
	 * @param renders  表格标题对应的字段集合
	 * @param dataList 需要显示的数据集合,集合中一定要放置符合JavaBean风格的类的对象。此方法支持的JavaBean属性的数据类型有基本数据类型及String,Date
	 * @param out      与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
	 * @param pattern  如果有时间数据，设定输出格式。默认为"dd/MM/yyyy hh:mm:ss"
	 */
	@SuppressWarnings({"unchecked", "rawtypes"})
	private void exportExcel2007(String title, List<String> headers, List<String> renders, Collection<T> dataList, OutputStream out, String pattern) {
		try {
			// 声明一个工作薄
			XSSFWorkbook workbook = new XSSFWorkbook();
			// 生成一个表格
			XSSFSheet sheet = workbook.createSheet(title);
			// 设置表格默认列宽度为15个字节
			sheet.setDefaultColumnWidth(20);
			// 生成标题样式
			XSSFCellStyle style = workbook.createCellStyle();
			// 设置标题样式
			style.setFillForegroundColor(new XSSFColor(java.awt.Color.gray));
			style.setFillPattern(SOLID_FOREGROUND);
			// 水平垂直居中
			style.setAlignment(HorizontalAlignment.CENTER);
			style.setVerticalAlignment(VerticalAlignment.CENTER);
			// 上下左右边框
			style.setBorderTop(BorderStyle.THIN);
			style.setBorderBottom(BorderStyle.THIN);
			style.setBorderLeft(BorderStyle.THIN);
			style.setBorderRight(BorderStyle.THIN);
			// 设置标题字体
			XSSFFont font = workbook.createFont();
			//font.setFontName("宋体");
			font.setColor(IndexedColors.WHITE.index);
			font.setFontHeightInPoints((short) 11);
			style.setFont(font);
			// 设置数据样式
			XSSFCellStyle style2 = workbook.createCellStyle();
			style2.setFillForegroundColor(new XSSFColor(java.awt.Color.WHITE));
			style2.setFillPattern(SOLID_FOREGROUND);
			style2.setAlignment(HorizontalAlignment.CENTER);
			style2.setVerticalAlignment(VerticalAlignment.CENTER);
			style2.setBorderTop(BorderStyle.THIN);
			style2.setBorderBottom(BorderStyle.THIN);
			style2.setBorderLeft(BorderStyle.THIN);
			style2.setBorderRight(BorderStyle.THIN);
			// 设置数据字体
			XSSFFont font2 = workbook.createFont();
			font2.setBold(true);
			style2.setFont(font2);
			// 产生表格标题行
			XSSFRow row = sheet.createRow(0);
			if (headers != null) {
				XSSFCell cellHeader;
				for (int i = 0; i < headers.size(); i++) {
					cellHeader = row.createCell(i);
					cellHeader.setCellStyle(style);
					cellHeader.setCellValue(new XSSFRichTextString(headers.get(i)));
				}
			}
			// 遍历集合数据，产生数据行
			Iterator<T> it = dataList.iterator();
			int index = 0;
			T t;
			Field field;
			XSSFRichTextString richString;
			Pattern p = Pattern.compile("^//d+(//.//d+)?$");
			Matcher matcher;
			XSSFCell cell;
			Object value;
			String textValue;
			SimpleDateFormat sdf = new SimpleDateFormat(pattern);
			while (it.hasNext()) {
				index++;
				row = sheet.createRow(index);
				t = (T) it.next();
				for (int i = 0; i < renders.size(); i++) {
					cell = row.createCell(i);
					cell.setCellStyle(style2);
					field = t.getClass().getDeclaredField(renders.get(i));
					field.setAccessible(true);
					value = field.get(t);
					// 判断值的类型后进行强制类型转换
					textValue = formatStringText(value, sdf);
					cell.setCellValue(textValue);
					if (textValue != null) {
						matcher = p.matcher(textValue);
						if (matcher.matches()) {
							cell.setCellValue(Double.parseDouble(textValue));
						} else {
							richString = new XSSFRichTextString(textValue);
							cell.setCellValue(richString);
						}
					}
				}
			}
			workbook.write(out);
		} catch (Exception e) {
			logger.error("Create Excel: {} Exception", title, e);
		}
	}

	/**
	 * 通用Excel导出方法,利用反射机制遍历对象的所有字段，将数据写入Excel文件中
	 * 此方法生成2003版本的excel,文件名后缀：xls
	 *
	 * @param title    表格标题名
	 * @param headers  表格头部标题集合
	 * @param renders  表格标题对应的字段集合
	 * @param dataList 需要显示的数据集合,集合中一定要放置符合JavaBean风格的类的对象。此方法支持的JavaBean属性的数据类型有基本数据类型及String,Date
	 * @param out      与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
	 * @param pattern  如果有时间数据，设定输出格式。默认为"dd/MM/yyyy hh:mm:ss"
	 */
	@SuppressWarnings({"unchecked", "rawtypes"})
	private void exportExcel2003(String title, List<String> headers, List<String> renders, Collection<T> dataList, OutputStream out, String pattern) {
		try {
			// 声明一个工作薄
			HSSFWorkbook workbook = new HSSFWorkbook();
			// 生成一个表格
			HSSFSheet sheet = workbook.createSheet(title);
			// 设置表格默认列宽度为15个字节
			sheet.setDefaultColumnWidth(20);
			// 生成标题样式
			HSSFCellStyle style = workbook.createCellStyle();
			// 设置标题样式
			style.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index);
			style.setFillPattern(SOLID_FOREGROUND);
			style.setAlignment(HorizontalAlignment.CENTER);
			style.setVerticalAlignment(VerticalAlignment.CENTER);
			style.setBorderTop(BorderStyle.THIN);
			style.setBorderBottom(BorderStyle.THIN);
			style.setBorderLeft(BorderStyle.THIN);
			style.setBorderRight(BorderStyle.THIN);
			// 设置标题字体
			HSSFFont font = workbook.createFont();
			//font.setFontName("宋体");
			font.setColor(IndexedColors.WHITE.index);
			font.setFontHeightInPoints((short) 11);
			style.setFont(font);
			// 设置数据样式
			HSSFCellStyle style2 = workbook.createCellStyle();
			style2.setFillForegroundColor(HSSFColor.WHITE.index);
			style2.setFillPattern(SOLID_FOREGROUND);
			style2.setAlignment(HorizontalAlignment.CENTER);
			style2.setVerticalAlignment(VerticalAlignment.CENTER);
			style2.setBorderTop(BorderStyle.THIN);
			style2.setBorderBottom(BorderStyle.THIN);
			style2.setBorderLeft(BorderStyle.THIN);
			style2.setBorderRight(BorderStyle.THIN);
			// 设置数据字体
			HSSFFont font2 = workbook.createFont();
			font2.setBold(true);
			style2.setFont(font2);
			// 产生表格标题行
			HSSFRow row = sheet.createRow(0);
			if (headers != null) {
				HSSFCell cellHeader;
				for (int i = 0; i < headers.size(); i++) {
					cellHeader = row.createCell(i);
					cellHeader.setCellStyle(style);
					cellHeader.setCellValue(new HSSFRichTextString(headers.get(i)));
				}
			}
			// 遍历集合数据，产生数据行
			Iterator<T> it = dataList.iterator();
			int index = 0;
			T t;
			Field field;
			HSSFRichTextString richString;
			Pattern p = Pattern.compile("^//d+(//.//d+)?$");
			Matcher matcher;
			HSSFCell cell;
			Object value;
			String textValue;
			SimpleDateFormat sdf = new SimpleDateFormat(pattern);
			while (it.hasNext()) {
				index++;
				row = sheet.createRow(index);
				t = (T) it.next();
				for (int i = 0; i < renders.size(); i++) {
					cell = row.createCell(i);
					cell.setCellStyle(style2);
					field = t.getClass().getDeclaredField(renders.get(i));
					field.setAccessible(true);
					value = field.get(t);
					// 判断值的类型后进行强制类型转换
					textValue = formatStringText(value, sdf);
					cell.setCellValue(textValue);
					if (textValue != null) {
						matcher = p.matcher(textValue);
						if (matcher.matches()) {
							cell.setCellValue(Double.parseDouble(textValue));
						} else {
							richString = new HSSFRichTextString(textValue);
							cell.setCellValue(richString);
						}
					}
				}
			}
			workbook.write(out);
		} catch (Exception e) {
			logger.error("Create Excel: {} Exception", title, e);
		}
	}

	private String formatStringText(Object value, SimpleDateFormat sdf) {
		String textValue = null;
		if (value instanceof Integer || value instanceof Float || value instanceof Double || value instanceof Long) {
			textValue = String.valueOf(value);
		} else if (value instanceof Boolean) {
			textValue = (Boolean) value ? "True" : "False";
		} else if (value instanceof Date) {
			textValue = sdf.format((Date) value);
		} else {
			if (value != null) {
				textValue = value.toString();
			}
		}
		return textValue;
	}
}
